-- Create tables for "estimates"
CREATE TABLE estimate (uidEstimate integer PRIMARY KEY,postingDate date not null,toFromText varchar,whatForText varchar, lastEditedDate date not null default 0, uidJournal integer not null default 0);
CREATE INDEX ix_estimate_postingDate on estimate(postingDate);
CREATE INDEX ix_estimate_lastEditedDate on estimate(lastEditedDate);
CREATE INDEX ix_estimate_journal on estimate(uidJournal);

CREATE TRIGGER tr_estimate_del AFTER DELETE ON estimate FOR EACH ROW
BEGIN
  delete from estimateLine where uidEstimate = old.uidEstimate;
END;

DROP TRIGGER tr_journal_del;

CREATE TRIGGER tr_journal_del AFTER DELETE ON journal FOR EACH ROW
BEGIN
  delete from journalLine where uidJournal = old.uidJournal;
  delete from statementLine where uidJournal = old.uidJournal;
  delete from timeEntry where uidJournal = old.uidJournal and isInvoice=1;
  update timeEntry set uidJournal = 0 where uidJournal = old.uidJournal;
  delete from payment where uidJournalInvoice = old.uidJournal;
  delete from payment where uidJournalTransfer = old.uidJournal;
  update estimate set uidJournal = 0 where uidJournal = old.uidJournal;
END;

CREATE TABLE estimateLine (uidEstimateLine integer primary key, uidEstimate integer not null, amount integer NOT NULL default 0, description varchar, uidAccountVat integer not null default 0, vatAmount integer not null default 0, lineNumber integer not null default 1, vatDirection integer not null default 1);
CREATE INDEX ix_estimateLine_estimate on estimateLine(uidEstimate);
CREATE INDEX ix_estimateLine_accountvat on estimateLine(uidAccountVat);
CREATE TRIGGER tr_estimateLine_del DELETE ON estimateLine 
BEGIN
  delete from estimateLineDetail where uidEstimateLine = old.uidEstimateLine;
END;
CREATE TRIGGER tr_estimateLine_c_ins BEFORE INSERT ON estimateLine FOR EACH ROW
BEGIN
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccountVat) and new.uidAccountVat <> 0
    then raise (abort, 'VAT account does not exist. FK:estimateLine:uidAccountVat')
  end;

  select case
    when not exists (select uidEstimate from estimate where uidEstimate = new.uidEstimate)
    then raise (abort, 'Estimate does not exist. FK:estimateLine:uidEstimate')
  end;
END;

CREATE TABLE estimateLineDetail (uidEstimateLineDetail integer primary key, uidEstimateLine integer not null, uidAccount integer not null, direction integer not null);
CREATE INDEX ix_estimateLineDetail_account on estimateLineDetail(uidAccount);
CREATE INDEX ix_estimateLineDetail_estimateLine on estimateLineDetail(uidEstimateLine);
CREATE TRIGGER tr_estimateLineDetail_c_ins BEFORE INSERT ON estimateLineDetail FOR EACH ROW
BEGIN
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccount)
    then raise (abort, 'Account does not exist. FK:estimateLineDetail:uidAccount')
  end;

  select case
    when not exists (select uidEstimateLine from estimateLine where uidEstimateLine = new.uidEstimateLine)
    then raise (abort, 'Transaction does not exist. FK:estimateLineDetail:uidEstimateLine')
  end;
END;

-- We don't need two triggers to do the same thing...
DROP TRIGGER IF EXISTS tr_account_del2;

-- Now if the an account is deleted, remove the estimates
DROP TRIGGER tr_account_del;

CREATE TRIGGER tr_account_del AFTER DELETE ON account FOR EACH ROW
BEGIN
  delete from accountDetail where uidAccount = old.uidAccount;
  delete from statement where uidAccount = old.uidAccount;
  delete from budget where uidAccount = old.uidAccount;
  delete from workerCustomerRel where uidAccount = old.uidAccount;
  delete from journal where uidJournal in (select jl.uidJournal
                                             from journalLine jl
                                            inner join journalLineDetail jld
                                               on jld.uidJournalLine = jl.uidJournalLine
                                            where jld.uidAccount = old.uidAccount );
  delete from journal where uidJournal in (select jl.uidJournal
                                             from journalLine jl
                                            where jl.uidAccountVat = old.uidAccount );
  delete from estimate where uidEstimate in (select el.uidEstimate
                                               from estimateLine el
                                              inner join estimateLineDetail eld
                                                 on eld.uidEstimateLine = el.uidEstimateLine
                                              where eld.uidAccount = old.uidAccount );
  delete from estimate where uidEstimate in (select el.uidEstimate
                                               from estimateLine el
                                              where el.uidAccountVat = old.uidAccount );
END;

-- Add setting to time-tracker to show rate name on invoices
alter table workerCustomerRel add column showOnInvoice integer not null default 0;

update workerCustomerRel 
   set showOnInvoice = 1 
 where uidWorkerCustomerRel in (select uidWorkerCustomerRel
                                  from workerRateCard
                                 where rateName like '%"%"%');

update workerRateCard set rateName = replace(rateName, '"', '');

INSERT INTO "tablemetadata" VALUES(20,'estimate',1,2);
INSERT INTO "tablemetadata" VALUES(21,'estimateLine',1,3);
INSERT INTO "tablemetadata" VALUES(22,'estimateLineDetail',1,4);

INSERT INTO "columnmetadata" VALUES(129,20,1,'uidEstimate',1,1,1);
INSERT INTO "columnmetadata" VALUES(131,20,2,'postingDate',3,0,1);
INSERT INTO "columnmetadata" VALUES(132,20,3,'toFromText',2,0,1);
INSERT INTO "columnmetadata" VALUES(133,20,4,'whatForText',2,0,1);
INSERT INTO "columnmetadata" VALUES(144,20,5,'lastEditedDate',3,0,1);
INSERT INTO "columnmetadata" VALUES(130,20,6,'uidJournal',1,0,1);

INSERT INTO "columnmetadata" VALUES(134,21,1,'uidEstimateLine',1,1,1);
INSERT INTO "columnmetadata" VALUES(135,21,2,'uidEstimate',1,0,1);
INSERT INTO "columnmetadata" VALUES(136,21,3,'amount',1,0,1);
INSERT INTO "columnmetadata" VALUES(137,21,4,'description',2,0,1);
INSERT INTO "columnmetadata" VALUES(138,21,5,'uidAccountVat',1,0,1);
INSERT INTO "columnmetadata" VALUES(139,21,6,'vatAmount',1,0,1);
INSERT INTO "columnmetadata" VALUES(145,21,7,'vatDirection',1,0,1);
INSERT INTO "columnmetadata" VALUES(148,21,8,'lineNumber',1,0,1);

INSERT INTO "columnmetadata" VALUES(140,22,1,'uidEstimateLineDetail',1,1,1);
INSERT INTO "columnmetadata" VALUES(141,22,2,'uidEstimateLine',1,0,1);
INSERT INTO "columnmetadata" VALUES(142,22,3,'uidAccount',1,0,1);
INSERT INTO "columnmetadata" VALUES(143,22,4,'direction',1,0,1);

INSERT INTO "columnmetadata" VALUES(149,14,17,'showOnInvoice',1,0,1);

insert into setting values(104,'ESTIMATE-TEXT');
insert into setting values(105,'ESTIMATE-NUMBER-TEXT');
insert into setting values(106,'ESTIMATE-DATE-TEXT');
insert into setting values(107,'ESTIMATE-TERMS');
insert into setting values(108,'PFA-SALE');
insert into setting values(109,'PFA-CREDIT');
insert into setting values(110,'PFA-PURCHASE-INV');
insert into setting values(111,'PFA-PURCHASE-CREDIT');
insert into setting values(112,'PFA-DELIVERY-NOTE');
insert into setting values(113,'PFA-RECEIPT');
insert into setting values(114,'PFA-REFUND');
insert into setting values(115,'PFA-REMITTANCE');
insert into setting values(116,'PFA-ESTIMATE');
insert into setting values(117,'PFA-STATEMENT');
insert into setting values(118,'TOTAL-ESTIMATE-TEXT');

-- sqlite3 -init schemaUpdateTo_0035.txt default.data

-- Update the schema
update systemSetting
   set settingValue = 35
 where uidSetting = 5;

